﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.SQLite;
using SMSModels;

namespace DAL
{
    public class SupplierService
    {
        /// <summary>
        /// 添加供货商
        /// </summary>
        /// <param name="supplier"></param>
        /// <returns></returns>
        public int AddSupplier(Supplier supplier)
        {
            //[1]封装SQL语句，添加供货商信息
            String sql = String.Format("insert into 供货商信息表 (供货商名称,联系人,联系电话,传真,地址,备注) values('{0}','{1}','{2}','{3}','{4}','{5}')", supplier.SupplierName, supplier.Linkman,supplier.Phone, supplier.Fax, supplier.Address, supplier.Remarks);
            String sqlStrIsExist = String.Format("select * from 供货商信息表 where 供货商名称='{0}'", supplier.SupplierName);

            //[2]提交插入
            if (SQLHelper.GetReader(sqlStrIsExist).HasRows)
            {
                return -1;

            }
            else
            {
                return SQLHelper.Update(sql);
            }

        }


        /// <summary>
        /// 删除供货商
        /// </summary>
        /// <param name="supplier"></param>
        /// <returns></returns>
        public int DeleteSupplier(Supplier supplier)
        {
            //[1]封装SQL语句，删除供货商信息
            String sql = String.Format("delete from 供货商信息表 where 供货商ID={0}", supplier.SupplierID);

            //[2]提交删除
            return SQLHelper.Update(sql);

        }


        /// <summary>
        /// 修改供货商信息
        /// </summary>
        /// <param name="supplier"></param>
        /// <returns></returns>
        public int UpdateSupplierInfo(Supplier supplier)
        {
            //[1] 封装SQL语句，修改供货商信息
            String sql = String.Format("update 供货商信息表 set 供货商名称='{0}', 联系人='{1}', 联系电话='{2}', 传真='{3}', 地址='{4}', 备注='{5}' where 供货商ID={6}", supplier.SupplierName,supplier.Linkman,supplier.Phone,supplier.Fax,supplier.Address,supplier.Remarks,supplier.SupplierID);

            //[2]提交修改
            return SQLHelper.Update(sql);
            

        }

        /// <summary>
        /// 获取供货商信息数据集对象
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSet()
        {
            String sql = "select * from 供货商信息表";
            String tbName = "supplierInfo";
            return SQLHelper.GetDataSet(sql, tbName);
        }


        ///// <summary>
        ///// 查询供货商ID及供货商名称，并封装到集合中
        ///// </summary>
        ///// <returns>返回封装的供货商集合list对象</returns>
        //public List<Supplier> GetSuppliersIdAndName()
        //{
        //    String sql = "select 供货商ID,供货商名称 from 供货商信息表";
        //    SQLiteDataReader reader = SQLHelper.GetReader(sql);
        //    List<Supplier> list = new List<Supplier>();
        //    while (reader.Read())
        //    {
        //        list.Add(new Supplier
        //        {
        //            SupplierID = Convert.ToInt32(reader["供货商ID"]),
        //            SupplierName = reader["供货商名称"].ToString()
        //        });
        //    }
        //    reader.Close();
        //    return list;

        //}
    }
}
